--
-- Definition of view `vw_page_list`
--
CREATE VIEW `vw_page_list` 
AS 
select `page`.`pageid` AS `pageid`,
`page`.`kbid` AS `kbid`,
`page`.`createdate` AS `createdate`,
`page`.`publishdate` AS `publishdate`,
`page`.`expiredate` AS `expiredate`,
`page`.`statusdate` AS `statusdate`,
`page`.`currentuserid` AS `currentuserid`,
`page`.`title` AS `title`,
`page`.`iv_access_type` AS `iv_access_type`,
`page`.`iv_level` AS `iv_level`,
`page`.`iv_status` AS `iv_status`,
`page`.`iv_priority` AS `iv_priority`,
`page`.`keywords` AS `keywords`,
`page`.`pagerevision` AS `pagerevision`,
`page`.`iv_itemtype` AS `iv_itemtype`,
`iv1`.`longdesc` AS `accesstype`,
`iv2`.`longdesc` AS `difficultylevel`,
`iv3`.`longdesc` AS `status`,
`iv4`.`longdesc` AS `priority`,
`iv5`.`longdesc` AS `itemtype`,
`page_summary`.`summary` AS `summary`,
`page`.`templateid` AS `templateid`,
`page`.`spotlight` AS `spotlight`,
`page`.`userid` AS `userid` 
from ((((((`page` left join `page_summary` on((`page_summary`.`pageid` = `page`.`pageid`))) 
left join `int_value` `iv1` on(((`iv1`.`int_value` = `page`.`iv_access_type`) and (`iv1`.`int_name` = 'iv_access_type')))) 
left join `int_value` `iv2` on(((`iv2`.`int_value` = `page`.`iv_level`) and (`iv2`.`int_name` = 'iv_level')))) 
left join `int_value` `iv3` on(((`iv3`.`int_value` = `page`.`iv_status`) and (`iv3`.`int_name` = 'iv_status')))) 
left join `int_value` `iv4` on(((`iv4`.`int_value` = `page`.`iv_priority`) and (`iv4`.`int_name` = 'iv_priority')))) 
left join `int_value` `iv5` on(((`iv5`.`int_value` = `page`.`iv_itemtype`) and (`iv5`.`int_name` = 'iv_itemtype'))));

--GO

--
-- Definition of view `vw_search_items`
--
CREATE VIEW `vw_search_items` 
AS 
select 1 AS `linktype`,
concat('?pid=blg_blogviewer&id=',`blog`.`blogid`) AS `urlparams`,
'blog' AS `item`,
`blog`.`blogid` AS `itemid`,
`blog`.`title` AS `title`,
`blog`.`body` AS `summary`,
`blog`.`body` AS `body`,
0 AS `spotlight`,
`blog`.`createdate` AS `createdate`,
ifnull(`blog`.`private`,0) AS `private`,
`blog`.`userid` AS `userid`,
1 AS `IsApproved`
from `blog`
union all 
select 2 AS `linktype`,
concat('?pid=eve_eventviewer&id=',`events`.`EventID`) AS `urlparams`,
'event' AS `item`,
`events`.`EventID` AS `itemid`,
`events`.`EventTitle` AS `title`,
`events`.`EventDetails` AS `summary`,
`events`.`EventDetails` AS `body`,
0 AS `spotlight`,
`events`.`EventDate` AS `createdate`,
ifnull(`events`.`Private`,0) AS `private`,
`events`.`UserID` AS `userid`,
1 AS `IsApproved`
from `events` 
union all 
select 3 AS `linktype`,
concat('?pid=main&view=1&id=',`news`.`newsid`) AS `urlparams`,
'news' AS `item`,
`news`.`newsid` AS `itemid`,
`news`.`title` AS `title`,
`news`.`body` AS `summary`,
`news`.`body` AS `body`,
0 AS `spotlight`,
`news`.`createdate` AS `createdate`,
0 AS `private`,
`news`.`userid` AS `userid`,
1 AS `IsApproved`
from `news` 
union all 
select 4 AS `linktype`,
concat('?pid=viewer&id=',`page_final_data`.`pageid`) AS `urlparams`,
'page' AS `item`,
`page`.`pageid` AS `itemid`,
`page`.`title` AS `title`,
`page_summary`.`summary` AS `summary`,
group_concat(`page_final_data`.`placeholderdata` separator ',') AS `body`,
ifnull(`page`.`spotlight`,0) AS `spotlight`,
`page`.`createdate` AS `createdate`,
(case `page`.`iv_access_type` when 2 then 0 else `page`.`iv_access_type` end) AS `private`,
`page`.`userid` AS `userid`,
1 AS `IsApproved` 
from ((((`page` left join `page_final_data` on((`page_final_data`.`pageid` = `page`.`pageid`))) 
left join `page_summary` on((`page_summary`.`pageid` = `page`.`pageid`))) 
left join `int_value` `iv_accesstype` on(((`iv_accesstype`.`int_value` = `page`.`iv_access_type`) and (`iv_accesstype`.`int_name` = 'iv_access_type')))) 
left join `int_value` `iv_level` on(((`iv_level`.`int_value` = `page`.`iv_level`) and (`iv_level`.`int_name` = 'iv_level')))) 
where (`page`.`iv_status` = 6) group by `page`.`pageid` 
union all 
select 5 AS `linktype`,
concat('?pid=res_download&resourceid=',`resources`.`resourceid`) AS `urlparams`,
'resource' AS `item`,
`resources`.`resourceid` AS `itemid`,
ifnull(`resources`.`resourcetitle`,
convert(`resources`.`resourcename` using utf8)) AS `title`,
`resources`.`resourcedesc` AS `summary`,
`resources`.`resourcedesc` AS `body`,
0 AS `spotlight`,
`resources`.`createdate` AS `createdate`,
ifnull(`resources`.`private`,0) AS `private`,
`resources`.`userid` AS `userid`,
1 AS `IsApproved` 
from `resources` 
union all select 6 AS `linktype`,
concat('?pid=for_post&topicid=',`forum_topic`.`TopicID`,'&forumid=',`forum_forum`.`ForumID`) AS `urlparams`,
'forum' AS `item`,
`forum_topic`.`TopicID` AS `itemid`,
`forum_topic`.`TopicName` AS `title`,
group_concat(`forum_post`.`Post` separator ',') AS `summary`,
group_concat(`forum_post`.`Post` separator ',') AS `body`,
0 AS `spotlight`,
(case when (`forum_topic`.`Created` <= `forum_topic`.`LastPostDate`) then `forum_topic`.`LastPostDate` else `forum_topic`.`Created` end) AS `createdate`,
0 AS `private`,
`forum_topic`.`LastUserID` AS `userid`,
IfNull(`forum_topic`.`isapproved`, 1) As `IsApproved`
from ((`forum_topic` join `forum_forum` on((`forum_forum`.`ForumID` = `forum_topic`.`ForumID`))) 
join `forum_post` on((`forum_post`.`TopicID` = `forum_topic`.`TopicID`))) 
group by `forum_topic`.`TopicID` 
union all 
select 7 AS `linktype`,
concat('?pid=pol_pollviewer&id=',`pollquestions`.`PollId`) AS `urlparams`,
'poll' AS `item`,
`pollquestions`.`PollId` AS `itemid`,
`pollquestions`.`PollQuestion` AS `title`,
`pollquestions`.`PollQuestion` AS `summary`,
`pollquestions`.`PollQuestion` AS `body`,
0 AS `spotlight`,
`pollquestions`.`CreateDate` AS `createdate`,
0 AS `private`,
`pollquestions`.`UserID` AS `userid`,
1 AS `IsApproved`  
from `pollquestions`;

--GO

--
-- Definition of view `vw_search_tags`
--
CREATE VIEW `vw_search_tags` 
AS 
select `tags`.`tagname` AS `tagname`,
`vw_search_items`.`urlparams` AS `urlparams`,
`vw_search_items`.`linktype` AS `linktype`,
`vw_search_items`.`item` AS `item`,
`vw_search_items`.`itemid` AS `itemid`,
`vw_search_items`.`title` AS `title`,
`vw_search_items`.`summary` AS `summary`,
`vw_search_items`.`body` AS `body`,
`vw_search_items`.`spotlight` AS `spotlight`,
`vw_search_items`.`createdate` AS `createdate`,
`vw_search_items`.`private` AS `private`,
`vw_search_items`.`userid` AS `userid`,
`fn_comment_count`(`vw_search_items`.`itemid`) AS `comments`,
`fn_view_count`(`vw_search_items`.`itemid`) AS `views`,
ifnull(`fn_rank_count`(`vw_search_items`.`itemid`),0) AS `rank`,
`vw_search_items`.`isapproved`
from (`vw_search_items` join `tags` on((`tags`.`itemid` = `vw_search_items`.`itemid`)));

--GO

--
-- Definition of view `vw_aspnet_Applications`
--
CREATE VIEW `vw_aspnet_Applications` AS
SELECT `aspnet_Applications`.`Description`, `aspnet_Applications`.`ApplicationId`, `aspnet_Applications`.`ApplicationName`
FROM aspnet_Applications;

--GO

--
-- Definition of view `vw_aspnet_MembershipUsers`
--
CREATE VIEW `vw_aspnet_MembershipUsers` AS
SELECT aspnet_Membership.LastPasswordChangedDate, aspnet_Membership.UserId, aspnet_Membership.PasswordFormat, aspnet_Membership.MobilePIN, aspnet_Membership.Email, aspnet_Membership.PasswordQuestion, aspnet_Membership.PasswordAnswer, aspnet_Membership.IsApproved, aspnet_Membership.LastLoginDate, aspnet_Membership.Comment, aspnet_Users.ApplicationId, aspnet_Users.UserName, aspnet_Users.MobileAlias, aspnet_Users.IsAnonymous, aspnet_Users.LastActivityDate, aspnet_Membership.CreateDate
FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId=aspnet_Users.UserId;

--GO

--
-- Definition of view `vw_aspnet_Profiles`
--
CREATE VIEW `vw_aspnet_Profiles` AS
SELECT (Length(aspnet_Profile.PropertyValuesString)+Length(aspnet_Profile.PropertyNames)), aspnet_Profile.LastUpdatedDate, aspnet_Profile.UserId
FROM aspnet_Profile;

--GO

--
-- Definition of view `vw_aspnet_Roles`
--
CREATE VIEW `vw_aspnet_Roles` AS
SELECT `aspnet_Roles`.`Description`, `aspnet_Roles`.`RoleName`, `aspnet_Roles`.`RoleId`, `aspnet_Roles`.`ApplicationId`
FROM aspnet_Roles;

--GO

--
-- Definition of view `vw_aspnet_Users`
--
CREATE VIEW `vw_aspnet_Users` AS
SELECT `aspnet_Users`.`LastActivityDate`, `aspnet_Users`.`IsAnonymous`, `aspnet_Users`.`MobileAlias`, `aspnet_Users`.`UserName`, `aspnet_Users`.`UserId`, `aspnet_Users`.`ApplicationId`
FROM aspnet_Users;

--GO

--
-- Definition of view `vw_aspnet_UsersInRoles`
--
CREATE VIEW `vw_aspnet_UsersInRoles` AS
SELECT `aspnet_UsersInRoles`.`RoleId`, `aspnet_UsersInRoles`.`UserId`
FROM aspnet_UsersInRoles;

--GO

--
-- Definition of view `vw_aspnet_WebPartState_Paths`
--
CREATE VIEW `vw_aspnet_WebPartState_Paths` AS
SELECT aspnet_Paths.Path, aspnet_Paths.PathId, aspnet_Paths.ApplicationId
FROM aspnet_Paths;

--GO

--
-- Definition of view `vw_aspnet_WebPartState_Shared`
--
CREATE VIEW `vw_aspnet_WebPartState_Shared` AS
SELECT aspnet_PagePersonalizationAllUsers.LastUpdatedDate, Length(aspnet_PagePersonalizationAllUsers.PageSettings), aspnet_PagePersonalizationAllUsers.PathId
FROM aspnet_PagePersonalizationAllUsers;

--GO

--
-- Definition of view `vw_aspnet_WebPartState_User`
--
CREATE VIEW `vw_aspnet_WebPartState_User` AS
SELECT aspnet_PagePersonalizationPerUser.LastUpdatedDate, Length(aspnet_PagePersonalizationPerUser.PageSettings), aspnet_PagePersonalizationPerUser.UserId, aspnet_PagePersonalizationPerUser.PathId
FROM aspnet_PagePersonalizationPerUser;